Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OLAP_EXPRESSION_TEXT Overview

`OLAP_EXPRESSION_TEXT` acts as a single-row character function within the context of a `SELECT FROM OLAP_TABLE` statement. You can specify `OLAP_EXPRESSION_TEXT` in the same way you specify other Oracle single-row functions, notably in the select list and `WHERE` and `ORDER BY` clauses.

## Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. Oracle supports a number of predefined single-row functions, for example `COS`, `LOG`, and `ROUND` which return numeric data, and `UPPER` and `LOWER` which return character data. For more information on single-row functions, refer to the Oracle Database SQL Reference.

The OLAP single-row functions, `OLAP_EXPRESSION` and its variants for text, date, and boolean data, return the result of an OLAP DML expression that you specify. The OLAP DML supports a rich syntax for specifying computations ranging from simple arithmetic expressions to statistical, financial, and time-series operations.

You can use `OLAP_EXPRESSION_TEXT` to dynamically calculate any valid text expression within an analytic workspace and retrieve its results. For more information on text expressions in the OLAP DML, search for "text expression" in the Oracle OLAP DML Reference.

## OLAP_EXPRESSION_TEXT and OLAP_TABLE

`OLAP_TABLE` uses a limit map to present the multidimensional data from an analytic workspace in tabular form. The limit map specifies the columns of the logical table. When an `OLAP_EXPRESSION_TEXT` function is specified in the select list of the query, `OLAP_TABLE` generates an additional column for the results of the function.

To use `OLAP_EXPRESSION_TEXT`, you must specify a `ROW2CELL` clause in the limit map used by `OLAP_TABLE`. `ROW2CELL` identifies a `RAW` column that `OLAP_TABLE` populates with information used by the OLAP single-row functions.

See Also: "Limit Maps" and "Limit Map: ROW2CELL Clause"

# OLAP_EXPRESSION_TEXT Syntax

The `OLAP_EXPRESSION_TEXT` function dynamically executes an OLAP DML text expression within the context of an `OLAP_TABLE` function.

Syntax

    OLAP_EXPRESSION_TEXT(
              r2c               IN   RAW(32),
              text_expression   IN   VARCHAR2)
          RETURN NUMBER;

Parameters

Table 33-1 OLAP_EXPRESSION_TEXT Function Parameters

Parameter | Description
--- | ---
`r2c` | The name of a column populated by a `ROW2CELL` clause in a call to `OLAP_TABLE`. `ROW2CELL` is a component of a limit map parameter of the `OLAP_TABLE` function. See "Limit Map Parameter".
`text_expression` | A text expression in the analytic workspace. Search for "text expression" in the Oracle OLAP DML Reference. See also "Guidelines for Using Quotation Marks in OLAP DML Commands".

Returns

An evaluation of text_expression for each row of the table object returned by the `OLAP_TABLE` function.

`OLAP_EXPRESSION_TEXT` returns character data. To return numeric, boolean, or date data, use the `OLAP_EXPRESSION`, `OLAP_EXPRESSION_BOOL`, or `OLAP_EXPRESSION_DATE` functions.

Example

Refer to "OLAP_EXPRESSION Examples" and "OLAP_EXPRESSION_BOOL Example" for examples of OLAP single-row functions.